package com.demo2do.fms.admin.entity.report.export;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.Map;
import java.util.WeakHashMap;

import org.springframework.jdbc.core.RowMapper;

import com.demo2do.core.persistence.support.QueryBuilder;
import com.demo2do.core.persistence.support.QueryFilter;
import com.demo2do.core.persistence.support.QueryOrder;

/**
 * 
 * @author sanz
 *
 */
public class ReimbursementReportSearch implements QueryBuilder {
    
    /** 运输公司 */
    private Long organizationId;
    
    /** 导出年月 */
    private String yearMonth;

    /** 起始行 */
    private Integer beginIndex;
    
    /** 截止行 */
    private Integer endIndex;
    
    private QueryFilter queryFilter;
    
    private QueryOrder queryOrder;
    
    /**
     * The default constructor
     */
    public ReimbursementReportSearch() {
        queryOrder = new QueryOrder("d.dispatch_order_code", "ASC");
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getPreparedFilter(boolean)
     */
    public QueryFilter getPreparedFilter(boolean refresh) {
        
        if (queryFilter == null || refresh) {
            
            queryFilter = new QueryFilter().addEquals("d", "organization_id", organizationId);
            
            queryFilter.addSentence("TO_CHAR(d.expense_reimburse_date, 'YYYYMM') = " + yearMonth, Collections.emptyMap());
            
        }
        
        return queryFilter;
        
    }
    
    /**
     * @return
     */
    public String buildCountTemplate() {
        String sentence = "SELECT COUNT(*) FROM tbl_truck_dispatch_order d, vw_vehicle a, tbl_truck_transport_cost b, tbl_all_employee_info dr1, tbl_all_employee_info dr2 WHERE d.is_disabled = 0 AND d.vehicle_id = a.id AND d.id = b.dispatch_order_id AND d.main_driver_id = dr1.id AND d.first_vice_driver_id = dr2.id(+)" + getPreparedFilter(false).clauseSentence();
        return sentence;
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildSentenceTemplate()
     */
    public String buildSentenceTemplate() {
        
        StringBuffer buffer = new StringBuffer(1024);
        
        buffer.append("SELECT d.dispatch_order_code AS dispatchOrderCode, ")
                     .append("d.shipment_group_id AS shipmentGroupId, ")
                     .append("d.actual_load_number AS actualLoadNumber, ")
                     .append("d.accomplish_confirm_number AS accomplishConfirmNumber, ")
                     .append("d.dispatch_date AS dispatchDate, ")
                     .append("FC_GET_LOAD_PLACE_NAME(d.start_facility_id) AS startFacility, ")
                     .append("FC_GET_LOAD_PLACE_NAME(d.destination_facility_id) AS destinationFacility, ")
                     .append("a.vehicle_name AS vehicleName, ")
                     .append("d.expense_reimburse_date AS expenseReimburseDate, ")
                     .append("dr1.name AS mainDriver, ")
                     .append("dr2.name AS firstDriver, ")
                     .append("d.facility_distance AS facilityDistance, ")
                     .append("b.full_load_distance AS fullLoadDistance, ")
                     .append("b.empty_load_distance AS emptyLoadDistance, ")
                     .append("b.main_driver_full_salary AS mainDriverFullSalary, ")
                     .append("b.main_driver_empty_salary AS mainDriverEmptySalary, ")
                     .append("b.main_driver_salary AS mainDriverSalary, ")
                     .append("b.road_fee AS roadFee, ")
                     .append("b.repair_fee AS repairFee, ")
                     .append("b.tyre_fee AS tyreFee, ")
                     .append("b.total_oil_fee_reimbursed AS totalOilFeeReimbursed, ")
                     .append("FC_GET_OTHER_FEE(d.id) AS otherFee, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 5372144) AS otherFee1, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 137655) AS otherFee2, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 137652) AS otherFee3, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 1767233) AS otherFee4, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 137602) AS otherFee5, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 875454) AS otherFee6, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 135719) AS otherFee7, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 167108) AS otherFee8, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 1724828) AS otherFee9, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 167106) AS otherFee10, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 875443) AS otherFee11, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 1724120) AS otherFee12, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 265989) AS otherFee13, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 1724768) AS otherFee14, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 6059342) AS otherFee15, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 137669) AS otherFee16, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 1802171) AS otherFee17, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 137663) AS otherFee18, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 74902764) AS otherFee19, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 77630081) AS otherFee20, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 74431626) AS otherFee21, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 252816353) AS otherFee22, ")
                     .append("FC_GET_OTHER_FEE_DETAIL(d.id, 10000) AS otherFee23 ")
              .append("FROM tbl_truck_dispatch_order d, vw_vehicle a, tbl_truck_transport_cost b, tbl_all_employee_info dr1, tbl_all_employee_info dr2 ")
              .append("WHERE d.is_disabled = 0 AND d.vehicle_id = a.id AND d.id = b.dispatch_order_id AND d.main_driver_id = dr1.id AND d.first_vice_driver_id = dr2.id(+)")
              .append(getPreparedFilter(false).clauseSentence() + queryOrder.sentence());
        
        String sentence = "SELECT page.*, ROWNUM rn FROM (" + buffer.toString() + ") page WHERE ROWNUM <= " + endIndex;
        sentence = "SELECT * FROM (" + sentence + ") WHERE rn >= " + beginIndex;
      
        return sentence;
        
    }
    
    /**
     * build rowMapper
     * 
     * @return
     */
    public RowMapper<Map<String, Object>> buildRowMapper() {
        
        RowMapper<Map<String, Object>> rowMapper = new RowMapper<Map<String,Object>>() {
            
            /* (non-Javadoc)
             * @see org.springframework.jdbc.core.RowMapper#mapRow(java.sql.ResultSet, int)
             */
            public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {
                
                Map<String, Object> result = new WeakHashMap<String, Object>();
                
                result.put("dispatchOrderCode", rs.getObject("dispatchOrderCode"));
                result.put("shipmentGroupId", rs.getObject("shipmentGroupId"));
                result.put("actualLoadNumber", rs.getObject("actualLoadNumber"));
                result.put("accomplishConfirmNumber", rs.getObject("accomplishConfirmNumber"));
                result.put("dispatchDate", rs.getObject("dispatchDate"));
                result.put("startFacility", rs.getObject("startFacility"));
                result.put("destinationFacility", rs.getObject("destinationFacility"));
                result.put("vehicleName", rs.getObject("vehicleName"));
                result.put("expenseReimburseDate", rs.getObject("expenseReimburseDate"));
                result.put("mainDriver", rs.getObject("mainDriver"));
                result.put("firstDriver", rs.getObject("firstDriver"));
                result.put("facilityDistance", rs.getObject("facilityDistance"));
                result.put("fullLoadDistance", rs.getObject("fullLoadDistance"));
                result.put("emptyLoadDistance", rs.getObject("emptyLoadDistance"));
                result.put("mainDriverFullSalary", rs.getObject("mainDriverFullSalary"));
                result.put("mainDriverEmptySalary", rs.getObject("mainDriverEmptySalary"));
                result.put("mainDriverSalary", rs.getObject("mainDriverSalary"));
                result.put("roadFee", rs.getObject("roadFee"));
                result.put("repairFee", rs.getObject("repairFee"));
                result.put("tyreFee", rs.getObject("tyreFee"));
                result.put("totalOilFeeReimbursed", rs.getObject("totalOilFeeReimbursed"));
                result.put("otherFee", rs.getObject("otherFee"));
                result.put("otherFee1", rs.getObject("otherFee1"));
                result.put("otherFee2", rs.getObject("otherFee2"));
                result.put("otherFee3", rs.getObject("otherFee3"));
                result.put("otherFee4", rs.getObject("otherFee4"));
                result.put("otherFee5", rs.getObject("otherFee5"));
                result.put("otherFee6", rs.getObject("otherFee6"));
                result.put("otherFee7", rs.getObject("otherFee7"));
                result.put("otherFee8", rs.getObject("otherFee8"));
                result.put("otherFee9", rs.getObject("otherFee9"));
                result.put("otherFee10", rs.getObject("otherFee10"));
                result.put("otherFee11", rs.getObject("otherFee11"));
                result.put("otherFee12", rs.getObject("otherFee12"));
                result.put("otherFee13", rs.getObject("otherFee13"));
                result.put("otherFee14", rs.getObject("otherFee14"));
                result.put("otherFee15", rs.getObject("otherFee15"));
                result.put("otherFee16", rs.getObject("otherFee16"));
                result.put("otherFee17", rs.getObject("otherFee17"));
                result.put("otherFee18", rs.getObject("otherFee18"));
                result.put("otherFee19", rs.getObject("otherFee19"));
                result.put("otherFee20", rs.getObject("otherFee20"));
                result.put("otherFee21", rs.getObject("otherFee21"));
                result.put("otherFee22", rs.getObject("otherFee22"));
                result.put("otherFee23", rs.getObject("otherFee23"));
                
                return result;
                
            }
            
        };
        
        return rowMapper;
        
    }

    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#buildParameters()
     */
    public Map<String, Object> buildParameters() {
        return getPreparedFilter(false).parameters();
    }

    /**
     * @return the organizationId
     */
    public Long getOrganizationId() {
        return organizationId;
    }
    
    /* (non-Javadoc)
     * @see com.demo2do.core.persistence.support.QueryBuilder#getOrder()
     */
    public QueryOrder getOrder() {
        return queryOrder;
    }

    /**
     * @return
     */
    public String getYearMonth() {
        return yearMonth;
    }

    /**
     * @return the beginIndex
     */
    public Integer getBeginIndex() {
        return beginIndex;
    }

    /**
     * @return the endIndex
     */
    public Integer getEndIndex() {
        return endIndex;
    }

    /**
     * @param organizationId the organizationId to set
     */
    public void setOrganizationId(Long organizationId) {
        this.organizationId = organizationId;
    }

    /**
     * @param yearMonth
     */
    public void setYearMonth(String yearMonth) {
        this.yearMonth = yearMonth;
    }

    /**
     * @param beginIndex the beginIndex to set
     */
    public void setBeginIndex(Integer beginIndex) {
        this.beginIndex = beginIndex;
    }

    /**
     * @param endIndex the endIndex to set
     */
    public void setEndIndex(Integer endIndex) {
        this.endIndex = endIndex;
    }
    
}